﻿using Dapper;
using ML.Entity;
using ML.IRepository;
using System.Linq;
using System.Collections.Generic;
using MySql.Data.MySqlClient;

namespace ML.Repository
{
    public class MerchantRepository : DBFactory, IMerchantRepository
    {
        public Pager<MerchantResponse> List(MerchantSearch merchantSearch)
        {
            //组装调用存储过程参数
            var criteria = new PageCriteria() { TableName = "ml_merchant m  INNER JOIN ml_user u on m.UserId = u.Id INNER JOIN ml_platformcategory pc ON m.PlatFormId = pc.Id" };
            if (!string.IsNullOrEmpty(merchantSearch.nickname_search))
            {
                criteria.Conditions += string.Format(" AND u.NickName LIKE '%{0}%'", merchantSearch.nickname_search);
            }
            if (!string.IsNullOrEmpty(merchantSearch.userId))
            {
                criteria.Conditions += string.Format(" AND m.UserId= '{0}'", merchantSearch.userId);
            }
            if (!string.IsNullOrEmpty(merchantSearch.merchantname_search))
            {
                criteria.Conditions += string.Format(" AND m.Name LIKE '%{0}%'", merchantSearch.merchantname_search);
            }
            if (merchantSearch.joinmark_search != -1)
            {
                criteria.Conditions += string.Format(" AND m.JoinMark={0}", merchantSearch.joinmark_search);
            }
            criteria.Conditions += string.Format(" AND m.DelMark=0");

            criteria.PageIndex = merchantSearch.pageindex;
            criteria.Field = @"u.NickName,pc.Name PlatFormName, m.*";
            criteria.PageSize = merchantSearch.pagesize;
            criteria.SortName = merchantSearch.ordername;
            if (merchantSearch.orderby)
            {
                criteria.Sort = "DESC";
            }
            else
            {
                criteria.Sort = "ASC";
            }

            //调用存储过程进行查询并且返回
            var list = GetPageData<MerchantResponse>(criteria);
            return list;
        }

        public MerchantEntity GetById(string id)
        {
            throw new System.NotImplementedException();
        }

        public MerchantEntity GetInfoByUserId(string userId)
        {
            const string sql = @"SELECT * FROM ml_merchant WHERE DelMark=0 AND UserId=@UserId";
            using (_db)
            {
                return _db.Query<MerchantEntity>(sql, new { @UserId = userId }).FirstOrDefault();
            }
        }

        public MerchantResponse GetDetailById(string id)
        {
            const string sql = @"SELECT u.NickName,pc.Name PlatFormName,m.* FROM ml_merchant m 
                INNER JOIN ml_user u on m.UserId = u.Id
                INNER JOIN ml_platformcategory pc ON m.PlatFormId=pc.Id WHERE m.Id=@Id";
            using (_db)
            {
                return _db.Query<MerchantResponse>(sql, new { @Id = id }).FirstOrDefault();
            }
        }

        public IEnumerable<MerchantEntity> GetListByPlatFormId(string id, int pageindex, int pagesize)
        {
            const string sql = @"SELECT * FROM ml_merchant WHERE PlatFormId = @Id AND JoinMark=1 AND DelMark=0 Limit @Start,@End";
            using (_db)
            {
                return _db.Query<MerchantEntity>(sql, new { @Id = id, @Start = (pageindex - 1) * pagesize, @End = pagesize });
            }
        }

        public IEnumerable<MerchantEntity> GetNoDelBusinessList(string name, int pageindex, int pagesize)
        {
            string sql = "";
            if (string.IsNullOrEmpty(name))
            {
                sql = @"SELECT * FROM ml_merchant WHERE JoinMark = 1 AND DelMark = 0 Limit @Start,@End";
            }
            else
            {
                sql = @"SELECT * FROM ml_merchant WHERE JoinMark = 1 AND DelMark = 0 AND Name LIKE @Name Limit @Start,@End";
            }
            using (_db)
            {
                return _db.Query<MerchantEntity>(sql, new { @Name = "%" + name + "%", @Start = (pageindex - 1) * pagesize, @End = pagesize });
            }
        }

        public MerchantEntity GetInfoById(string Id)
        {
            const string sql = @"SELECT * FROM ml_merchant WHERE JoinMark = 1 AND DelMark = 0 AND Id = @Id ";
            using (_db)
            {
                return _db.Query<MerchantEntity>(sql, new { @Id = Id }).FirstOrDefault();
            }
        }

        public IEnumerable<ProductEntity> GetProductCategoryAndInfoById(List<string> productcategory_ids)
        {
            const string sql = @"SELECT * FROM ml_product WHERE UALMark=1 AND DelMark=0 AND CategoryId IN @CategoryIds";
            using (_db)
            {
                return _db.Query<ProductEntity>(sql, new { @CategoryIds = productcategory_ids.ToArray() });
            }
        }

        public bool Add(MerchantEntity entity, CapitalAccountEntity capitalEntity)
        {
            const string sql = @"INSERT INTO ml_merchant(Id,UserId,PlatFormId,Name,Logo,Notice,Phone,Address,BusinessStartHours,BusinessEndHours,DistributionFee,JoinMark,DelMark,CreateTime) 
                VALUES (@Id,@UserId,@PlatFormId,@Name,@Logo,@Notice,@Phone,@Address,@BusinessStartHours,@BusinessEndHours,@DistributionFee,@JoinMark,@DelMark,@CreateTime);";
            const string sqlca = @"INSERT INTO ml_capitalaccount(Id,MerchantId,Balance,Withdrawed,DelMark,UpdateTime,CreateTime) 
                VALUES (@Id,@MerchantId,@Balance,@Withdrawed,@DelMark,@UpdateTime,@CreateTime);";

            //构造事务、要么执行成功要么执行失败
            using (var conn = OpenConnection())
            {
                MySqlTransaction trans = conn.BeginTransaction();
                var isMerchantTrue = conn.Execute(sql, entity, trans);
                if (isMerchantTrue > 0)
                {
                    var iscapitalTrue = conn.Execute(sqlca, capitalEntity, trans);
                    if (iscapitalTrue < 0)
                    {
                        trans.Rollback();
                        return false;
                    }
                    trans.Commit();
                }
                else
                {
                    trans.Rollback();
                    return false;
                }
                return true;
            }
        }

        public bool Add(MerchantEntity entity)
        {
            throw new System.NotImplementedException();
        }

        public bool Update(MerchantEntity entity)
        {
            const string sql = @"UPDATE ml_merchant Set PlatFormId=@PlatFormId,Name=@Name,Logo=@Logo,Notice=@Notice,Phone=@Phone,
Address=@Address,BusinessStartHours=@BusinessStartHours,BusinessEndHours=@BusinessEndHours,DistributionFee=@DistributionFee,
Lat=@Lat,Lon=@Lon,MlDescribe=@MlDescribe,UpdateTime=@UpdateTime WHERE Id=@Id";
            using (_db)
            {
                if (_db.Execute(sql, entity) > 0)
                {
                    return true;
                }
                return false;
            }
        }

        public bool UpdateBusinessState(string Id, string JoinMark)
        {
            const string sql = @"UPDATE ml_merchant SET JoinMark = @JoinMark WHERE Id=@Id";
            using (_db)
            {
                if (_db.Execute(sql, new { @JoinMark = JoinMark, @Id = Id }) > 0)
                {
                    return true;
                }
                return false;
            }
        }

        public IEnumerable<MerchantEntity> GetAll()
        {
            const string sql = @"SELECT * FROM ml_merchant";
            using (_db)
            {
                return _db.Query<MerchantEntity>(sql);
            }
        }

        public bool Delete(MerchantEntity entity)
        {
            throw new System.NotImplementedException();
        }

        public bool Delete(string id)
        {
            throw new System.NotImplementedException();
        }

        public IEnumerable<MerchantEntity> GetNoDelBusinessListByRating(string name, int pageindex, int pagesize, int ratingType, bool isDesc)
        {
            string sql = "";
            if (string.IsNullOrEmpty(name))
            {
                //综合评分
                switch (ratingType)
                {
                    case 0:
                        sql = isDesc ? @"SELECT * FROM ml_merchant WHERE JoinMark = 1 AND DelMark = 0 ORDER BY OverallRating DESC Limit @Start,@End" : @"SELECT * FROM ml_merchant WHERE JoinMark = 1 AND DelMark = 0 ORDER BY OverallRating Limit @Start,@End";
                        break;
                    case 1:
                        sql = isDesc ? @"SELECT * FROM ml_merchant WHERE JoinMark = 1 AND DelMark = 0 ORDER BY SpeedRating DESC Limit @Start,@End" : @"SELECT * FROM ml_merchant WHERE JoinMark = 1 AND DelMark = 0 ORDER BY SpeedRating Limit @Start,@End";
                        break;
                    case 2:
                        sql = isDesc ? @"SELECT * FROM ml_merchant WHERE JoinMark = 1 AND DelMark = 0 ORDER BY SalesCount DESC Limit @Start,@End" : @"SELECT * FROM ml_merchant WHERE JoinMark = 1 AND DelMark = 0 ORDER BY SalesCount Limit @Start,@End";
                        break;
                }
                //速度评分
                //销量
            }
            else
            {

                switch (ratingType)
                {
                    //综合评分
                    case 0:
                        sql = isDesc ? @"SELECT * FROM ml_merchant WHERE JoinMark = 1 AND DelMark = 0 AND Name LIKE @Name ORDER BY OverallRating DESC Limit @Start,@End" : @"SELECT * FROM ml_merchant WHERE JoinMark = 1 AND DelMark = 0 AND Name LIKE @Name ORDER BY OverallRating Limit @Start,@End";
                        break;
                    //速度评分
                    case 1:
                        sql = isDesc ? @"SELECT * FROM ml_merchant WHERE JoinMark = 1 AND DelMark = 0 AND Name LIKE @Name ORDER BY SpeedRating DESC Limit @Start,@End" : @"SELECT * FROM ml_merchant WHERE JoinMark = 1 AND DelMark = 0 AND Name LIKE @Name ORDER BY SpeedRating Limit @Start,@End";
                        break;
                    //销量
                    case 2:
                        sql = isDesc ? @"SELECT * FROM ml_merchant WHERE JoinMark = 1 AND DelMark = 0 AND Name LIKE @Name ORDER BY SalesCount DESC Limit @Start,@End" : @"SELECT * FROM ml_merchant WHERE JoinMark = 1 AND DelMark = 0 AND Name LIKE @Name ORDER BY SalesCount Limit @Start,@End";
                        break;
                }

            }
            using (_db)
            {
                return _db.Query<MerchantEntity>(sql, new { @Name = "%" + name + "%", @Start = (pageindex - 1) * pagesize, @End = pagesize });
            }
        }
    }
}